package cn.mq;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import static cn.mq.DateConversion.convertDateFormat;

import static cn.mq.constant.*;


public class InsertIntoMysqlUtil {
    private static final Logger logger = Logger.getLogger(InsertIntoMysqlUtil.class); // 创建 Logger 实例

    /**
     * 将发送批售信息插入到Mysql
     *
     * @param jsonString
     */
    public static void insertSendWholesaleInfo(String jsonString) {
        JSONObject jsonObj = JSON.parseObject(jsonString);
        JSONObject requestHead = jsonObj.getJSONObject("requestHead");
        JSONArray dataArray = jsonObj.getJSONObject("requestBody").getJSONArray("data");
        JSONObject requestBody = dataArray.getJSONObject(0);

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            String sql = "insert into SendWholesaleInfo" +
                    " (requestId,sourceSystem,targetSystem,serviceName,sendTime,brand,bolType,seqNo,bizCode,bizType," +
                    "carOmdId,vin,seriesCode,modelCode,modelVersion,colorCode,interiorCode,optionCode,dealerCode," +
                    "dealerName,salesStatus,salesRetFlag,rateCode,rateType,rsscCode,modelYear,bulkOrderDate) " +
                    "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setString(1, requestHead.getString("requestId"));
                stmt.setString(2, requestHead.getString("sourceSystem"));
                stmt.setString(3, requestHead.getString("targetSystem"));
                stmt.setString(4, requestHead.getString("serviceName"));
                stmt.setString(5, requestHead.getString("sendTime"));

                stmt.setString(6, requestBody.getString("brand"));
                stmt.setString(7, requestBody.getString("bolType"));
                stmt.setString(8, requestBody.getString("seqNo"));
                stmt.setString(9, requestBody.getString("bizCode"));
                stmt.setString(10, requestBody.getString("bizType"));
                stmt.setString(11, requestBody.getString("carOmdId"));
                stmt.setString(12, requestBody.getString("vin"));
                stmt.setString(13, requestBody.getString("seriesCode"));
                stmt.setString(14, requestBody.getString("modelCode"));
                stmt.setString(15, requestBody.getString("modelVersion"));
                stmt.setString(16, requestBody.getString("colorCode"));
                stmt.setString(17, requestBody.getString("interiorCode"));
                stmt.setString(18, requestBody.getString("optionCode"));
                stmt.setString(19, requestBody.getString("dealerCode"));
                stmt.setString(20, requestBody.getString("dealerName"));
                stmt.setString(21, requestBody.getString("salesStatus"));
                stmt.setString(22, requestBody.getString("salesRetFlag"));
                stmt.setString(23, requestBody.getString("rateCode"));
                stmt.setString(24, requestBody.getString("rateType"));
                stmt.setString(25, requestBody.getString("rsscCode"));
                stmt.setString(26, requestBody.getString("modelYear"));
                stmt.setString(27, requestBody.getString("bulkOrderDate"));

                stmt.executeUpdate();
                stmt.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 将零售开票信息数据插入到Mysql
     *
     * @param jsonString
     */
    public static void insertSendElectronicInvoice(String jsonString) {

        JSONObject jsonObj = JSON.parseObject(jsonString);
        JSONArray jsonArray = jsonObj.getJSONArray("data");

//        logger.info("********************************************************************************************************************************");
//        logger.info("********************************************************************************************************************************");
//
//        logger.info(jsonArray);
//
//        logger.info("********************************************************************************************************************************");
//        logger.info("********************************************************************************************************************************");


        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            String sql = "replace into ods_omd_tt_retail_invoice_df" +
                    " ( brand_code ,retail_type ,company_code ,brand_name ,dealer_code ,dealer_name ,bill_invoice_no ,dlr_rtl_ord_code ," +
                    "nga_deposit_order_code ,mer_order_id ,mer_cust_id ,cust_name ,cust_tel ,cust_id_no ,commission_no ,vin ,accb_model_name ," +
                    "model_code ,accb_type_code ,accb_type_name ,model_year ,model_version ,interior_code ,pr_list ,color_code ,vehicle_type ," +
                    "factory_plate_model ,produce_place ,cert_no ,import_prove_no ,commodity_inspection_no ,engine_no ,tonnage ," +
                    "passenger_limit_num ,is_loan ,invoice_type ,invoice_code ,invoice_no ,invoice_date ,seller_name ,seller_phone ,seller_tax_number ," +
                    "seller_account ,seller_address ,seller_bank ,invoice_id_no ,invoice_name ,invoice_tel ,invoice_tax_no ,total_amount ,rebate_amount ," +
                    "total_tax_amount ,tax_free_amount ,tax_amount ,tax_rate ,invoice_num ,tax_class_code ,tax_class_short_name ,tax_office_code ," +
                    "intact_certificate_num ,invoice_status ,original_dpt_invoice_no ,warranty_sign ,owner_province_code ,owner_city_code ," +
                    "owner_county_code ,owner_pos_no ,contact_name ,contact_sex ,contact_phone ,contact_tel ,contact_province_code ," +
                    "producer_date ,producer_name ,producer_address ,producer_pos_no ,producer_tel ,sales_name ,sales_address ,sales_pos_code ," +
                    "sales_tel ,network_information ,invoice_id_type ,biz_type ,pot_cust_id ,create_time ,deposit_date ) " +
                    "values (? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,lower(hex(aes_encrypt(?, 'IG17onG/IRDSsos9')))  ,lower(hex(aes_encrypt(?, 'IG17onG/IRDSsos9'))) ," +
                    "lower(hex(aes_encrypt(?, 'IG17onG/IRDSsos9'))) ,? ,lower(hex(aes_encrypt(?, 'IG17onG/IRDSsos9'))) ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? " +
                    ",? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,lower(hex(aes_encrypt(?, 'IG17onG/IRDSsos9'))) ,lower(hex(aes_encrypt(?, 'IG17onG/IRDSsos9'))) ," +
                    "lower(hex(aes_encrypt(?, 'IG17onG/IRDSsos9'))) ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ," +
                    "? ,? ,? ,? ,? ,? ,? ,?)";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {

                for (int i = 0; i < jsonArray.size() ; i++) {
                    JSONObject requestBody = jsonArray.getJSONObject(i);


                    // brandCode 和 billInvoiceNo作为主键,不能为空
                    stmt.setString(1, requestBody.getString("brandCode"));
                    stmt.setString(2, requestBody.containsKey("retailType") ? requestBody.getString("retailType") : null);
                    stmt.setString(3, requestBody.containsKey("companyCode") ? requestBody.getString("companyCode") : null);
                    stmt.setString(4, requestBody.containsKey("brandName") ? requestBody.getString("brandName") : null);
                    stmt.setString(5, requestBody.containsKey("dealerCode") ? requestBody.getString("dealerCode") : null);
                    stmt.setString(6, requestBody.containsKey("dealerName") ? requestBody.getString("dealerName") : null);
                    stmt.setString(7, requestBody.getString("billInvoiceNo"));
                    stmt.setString(8, requestBody.containsKey("dlrRtlOrdCode") ? requestBody.getString("dlrRtlOrdCode") : null);
                    stmt.setString(9, requestBody.containsKey("ngaDepositOrderCode") ? requestBody.getString("ngaDepositOrderCode") : null);
                    stmt.setString(10, requestBody.containsKey("merOrderId") ? requestBody.getString("merOrderId") : null);
                    stmt.setString(11, requestBody.containsKey("merCustId") ? requestBody.getString("merCustId") : null);
                    stmt.setString(12, requestBody.containsKey("custName") ? requestBody.getString("custName") : null);
                    stmt.setString(13, requestBody.containsKey("custTel") ? requestBody.getString("custTel") : null);
                    stmt.setString(14, requestBody.containsKey("custIdNo") ? requestBody.getString("custIdNo") : null);
                    stmt.setString(15, requestBody.containsKey("commissionNo") ? requestBody.getString("commissionNo") : null);
                    stmt.setString(16, requestBody.containsKey("vin") ? requestBody.getString("vin") : null);
                    stmt.setString(17, requestBody.containsKey("accbModelName") ? requestBody.getString("accbModelName") : null);
                    stmt.setString(18, requestBody.containsKey("modelCode") ? requestBody.getString("modelCode") : null);
                    stmt.setString(19, requestBody.containsKey("accbTypeCode") ? requestBody.getString("accbTypeCode") : null);
                    stmt.setString(20, requestBody.containsKey("accbTypeName") ? requestBody.getString("accbTypeName") : null);
                    stmt.setString(21, requestBody.containsKey("modelYear") ? requestBody.getString("modelYear") : null);
                    stmt.setString(22, requestBody.containsKey("modelVersion") ? requestBody.getString("modelVersion") : null);
                    stmt.setString(23, requestBody.containsKey("interiorCode") ? requestBody.getString("interiorCode") : null);
                    stmt.setString(24, requestBody.containsKey("prList") ? requestBody.getString("prList") : null);
                    stmt.setString(25, requestBody.containsKey("colorCode") ? requestBody.getString("colorCode") : null);
                    stmt.setString(26, requestBody.containsKey("vehicleType") ? requestBody.getString("vehicleType") : null);
                    stmt.setString(27, requestBody.containsKey("factoryPlateModel") ? requestBody.getString("factoryPlateModel") : null);
                    stmt.setString(28, requestBody.containsKey("producePlace") ? requestBody.getString("producePlace") : null);
                    stmt.setString(29, requestBody.containsKey("certNo") ? requestBody.getString("certNo") : null);
                    stmt.setString(30, requestBody.containsKey("importProveNo") ? requestBody.getString("importProveNo") : null);
                    stmt.setString(31, requestBody.containsKey("commodityInspectionNo") ? requestBody.getString("commodityInspectionNo") : null);
                    stmt.setString(32, requestBody.containsKey("engineNo") ? requestBody.getString("engineNo") : null);
                    stmt.setString(33, requestBody.containsKey("tonnage") ? requestBody.getString("tonnage") : null);
                    stmt.setString(34, requestBody.containsKey("passengerLimitNum") ? requestBody.getString("passengerLimitNum") : null);
                    stmt.setString(35, requestBody.containsKey("isLoan") ? requestBody.getString("isLoan") : null);
                    stmt.setString(36, requestBody.containsKey("invoiceType") ? requestBody.getString("invoiceType") : null);
                    stmt.setString(37, requestBody.containsKey("invoiceCode") ? requestBody.getString("invoiceCode") : null);
                    stmt.setString(38, requestBody.containsKey("invoiceNo") ? requestBody.getString("invoiceNo") : null);

                    stmt.setString(39, requestBody.containsKey("invoiceDate") ? convertDateFormat(requestBody.getString("invoiceDate")) : null);
                    stmt.setString(40, requestBody.containsKey("sellerName") ? requestBody.getString("sellerName") : null);
                    stmt.setString(41, requestBody.containsKey("sellerPhone") ? requestBody.getString("sellerPhone") : null);
                    stmt.setString(42, requestBody.containsKey("sellerTaxNumber") ? requestBody.getString("sellerTaxNumber") : null);
                    stmt.setString(43, requestBody.containsKey("sellerAccount") ? requestBody.getString("sellerAccount") : null);
                    stmt.setString(44, requestBody.containsKey("sellerAddress") ? requestBody.getString("sellerAddress") : null);
                    stmt.setString(45, requestBody.containsKey("sellerBank") ? requestBody.getString("sellerBank") : null);
                    stmt.setString(46, requestBody.containsKey("invoiceIdNo") ? requestBody.getString("invoiceIdNo") : null);
                    stmt.setString(47, requestBody.containsKey("invoiceName") ? requestBody.getString("invoiceName") : null);
                    stmt.setString(48, requestBody.containsKey("invoiceTel") ? requestBody.getString("invoiceTel") : null);
                    stmt.setString(49, requestBody.containsKey("invoiceTaxNo") ? requestBody.getString("invoiceTaxNo") : null);
                    stmt.setString(50, requestBody.containsKey("totalAmount") ? requestBody.getString("totalAmount") : null);
                    stmt.setString(51, requestBody.containsKey("rebateAmount") ? requestBody.getString("rebateAmount") : null);
                    stmt.setString(52, requestBody.containsKey("totalTaxAmount") ? requestBody.getString("totalTaxAmount") : null);
                    stmt.setString(53, requestBody.containsKey("taxFreeAmount") ? requestBody.getString("taxFreeAmount") : null);
                    stmt.setString(54, requestBody.containsKey("taxAmount") ? requestBody.getString("taxAmount") : null);
                    stmt.setString(55, requestBody.containsKey("taxRate") ? requestBody.getString("taxRate") : null);
                    stmt.setString(56, requestBody.containsKey("invoiceNum") ? requestBody.getString("invoiceNum") : null);
                    stmt.setString(57, requestBody.containsKey("taxClassCode") ? requestBody.getString("taxClassCode") : null);
                    stmt.setString(58, requestBody.containsKey("taxClassShortName") ? requestBody.getString("taxClassShortName") : null);
                    stmt.setString(59, requestBody.containsKey("taxOfficeCode") ? requestBody.getString("taxOfficeCode") : null);
                    stmt.setString(60, requestBody.containsKey("intactCertificateNum") ? requestBody.getString("intactCertificateNum") : null);
                    stmt.setString(61, requestBody.containsKey("invoiceStatus") ? requestBody.getString("invoiceStatus") : null);
                    stmt.setString(62, requestBody.containsKey("originalDptInvoiceNo") ? requestBody.getString("originalDptInvoiceNo") : null);
                    stmt.setString(63, requestBody.containsKey("warrantySign") ? requestBody.getString("warrantySign") : null);
                    stmt.setString(64, requestBody.containsKey("ownerProvinceCode") ? requestBody.getString("ownerProvinceCode") : null);
                    stmt.setString(65, requestBody.containsKey("ownerCityCode") ? requestBody.getString("ownerCityCode") : null);
                    stmt.setString(66, requestBody.containsKey("ownerCountyCode") ? requestBody.getString("ownerCountyCode") : null);
                    stmt.setString(67, requestBody.containsKey("ownerPosNo") ? requestBody.getString("ownerPosNo") : null);
                    stmt.setString(68, requestBody.containsKey("contactName") ? requestBody.getString("contactName") : null);
                    stmt.setString(69, requestBody.containsKey("contactSex") ? requestBody.getString("contactSex") : null);
                    stmt.setString(70, requestBody.containsKey("contactPhone") ? requestBody.getString("contactPhone") : null);
                    stmt.setString(71, requestBody.containsKey("contactTel") ? requestBody.getString("contactTel") : null);
                    stmt.setString(72, requestBody.containsKey("contactProvinceCode") ? requestBody.getString("contactProvinceCode") : null);

                    stmt.setString(73, requestBody.containsKey("producerDate") ? convertDateFormat(requestBody.getString("producerDate")) : null);
                    stmt.setString(74, requestBody.containsKey("producerName") ? requestBody.getString("producerName") : null);
                    stmt.setString(75, requestBody.containsKey("producerAddress") ? requestBody.getString("producerAddress") : null);
                    stmt.setString(76, requestBody.containsKey("producerPosNo") ? requestBody.getString("producerPosNo") : null);
                    stmt.setString(77, requestBody.containsKey("producerTel") ? requestBody.getString("producerTel") : null);
                    stmt.setString(78, requestBody.containsKey("salesName") ? requestBody.getString("salesName") : null);
                    stmt.setString(79, requestBody.containsKey("salesAddress") ? requestBody.getString("salesAddress") : null);
                    stmt.setString(80, requestBody.containsKey("salesPosCode") ? requestBody.getString("salesPosCode") : null);
                    stmt.setString(81, requestBody.containsKey("salesTel") ? requestBody.getString("salesTel") : null);
                    stmt.setString(82, requestBody.containsKey("networkinformation") ? requestBody.getString("networkinformation") : null);
                    stmt.setString(83, requestBody.containsKey("invoiceIdType") ? requestBody.getString("invoiceIdType") : null);
                    stmt.setString(84, requestBody.containsKey("bizType") ? requestBody.getString("bizType") : null);
                    stmt.setString(85, requestBody.containsKey("potCustId") ? requestBody.getString("potCustId") : null);
                    // 获取当前时间并转换为 CST 时区
                    ZonedDateTime nowInCST = ZonedDateTime.now(ZoneId.of("Asia/Shanghai"));
                    LocalDateTime localDateTime = nowInCST.toLocalDateTime();
                    Timestamp timestamp = Timestamp.valueOf(localDateTime);
                    stmt.setString(86, String.valueOf(timestamp));

                    stmt.setString(87, requestBody.containsKey("depositDate") ? convertDateFormat(requestBody.getString("depositDate")) : null);

                    // 添加批处理
                    stmt.addBatch();
                    // 每隔一定量的批次执行，避免内存过高
                    if (i % 50 == 0) {
                        stmt.executeBatch();    // 执行批处理
                        stmt.clearBatch();     // 清空批处理
                    }
                }
                // 最后一批可能未满的批次提交
                stmt.executeBatch();
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 将发票信息插入到Mysql
     *
     * @param jsonString
     */
    public static void insertNGAInvoice(String jsonString) {
        JSONObject jsonObj = JSON.parseObject(jsonString);
        JSONObject requestHead = jsonObj.getJSONObject("requestHead");
        JSONArray dataArray = jsonObj.getJSONObject("requestBody").getJSONArray("data");
        JSONObject requestBody = dataArray.getJSONObject(0);

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {

            String sql = "insert into NGAInvoceInfo " +
                    "(requestId,sourceSystem,targetSystem,serviceName,transTime,brandCode,companyCode,dealerCode,dealerName," +
                    "ngaDptInvoiceNo,merOrderId,merCustId,custName,custTel,custIdNo,commissionNo,vin,identityCode,modelCode," +
                    "modelName,modelYear,modelVersion,interiorCode,prList,colorCode,vehicleType,factoryPlateModel,producePlace," +
                    "certNo,importProveNo,commodityInspectionNo,engineNo,tonnage,passengerLimitNum,isLoan,invoiceType," +
                    "invoiceCode,invoiceNo,invoiceDate,sellerName,sellerPhone,sellerTaxNumber,sellerAccount,sellerAddress" +
                    ",sellerBank,invoiceIdNo,invoiceName,invoiceTel,invoiceTaxNo,totalAmount,rebateAmount,totalTaxAmount" +
                    ",taxFreeAmount,taxAmount,taxRate,invoiceNum,taxClassCode,taxClassShortName,taxOfficeCode," +
                    "intactCertificateNum,invoiceStatus,originalNgaDptInvoiceNo,warrantySign) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
                    "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setString(1, requestHead.getString("requestId"));
                stmt.setString(2, requestHead.getString("sourceSystem"));
                stmt.setString(3, requestHead.getString("targetSystem"));
                stmt.setString(4, requestHead.getString("serviceName"));
                stmt.setString(5, requestHead.getString("transTime"));

                stmt.setString(6, requestBody.getString("brandCode"));
                stmt.setString(7, requestBody.getString("companyCode"));
                stmt.setString(8, requestBody.getString("dealerCode"));
                stmt.setString(9, requestBody.getString("dealerName"));
                stmt.setString(10, requestBody.getString("ngaDptInvoiceNo"));
                stmt.setString(11, requestBody.getString("merOrderId"));
                stmt.setString(12, requestBody.getString("merCustId"));
                stmt.setString(13, requestBody.getString("custName"));
                stmt.setString(14, requestBody.getString("custTel"));
                stmt.setString(15, requestBody.getString("custIdNo"));
                stmt.setString(16, requestBody.getString("commissionNo"));
                stmt.setString(17, requestBody.getString("vin"));
                stmt.setString(18, requestBody.getString("identityCode"));
                stmt.setString(19, requestBody.getString("modelCode"));
                stmt.setString(20, requestBody.getString("modelName"));
                stmt.setString(21, requestBody.getString("modelYear"));
                stmt.setString(22, requestBody.getString("modelVersion"));
                stmt.setString(23, requestBody.getString("interiorCode"));
                stmt.setString(24, requestBody.getString("prList"));
                stmt.setString(25, requestBody.getString("colorCode"));
                stmt.setString(26, requestBody.getString("vehicleType"));
                stmt.setString(27, requestBody.getString("factoryPlateModel"));
                stmt.setString(28, requestBody.getString("producePlace"));
                stmt.setString(29, requestBody.getString("certNo"));
                stmt.setString(30, requestBody.getString("importProveNo"));
                stmt.setString(31, requestBody.getString("commodityInspectionNo"));
                stmt.setString(32, requestBody.getString("engineNo"));
                stmt.setString(33, requestBody.getString("tonnage"));
                stmt.setString(34, requestBody.getString("passengerLimitNum"));
                stmt.setString(35, requestBody.getString("isLoan"));
                stmt.setString(36, requestBody.getString("invoiceType"));
                stmt.setString(37, requestBody.getString("invoiceCode"));
                stmt.setString(38, requestBody.getString("invoiceNo"));
                stmt.setString(39, requestBody.getString("invoiceDate"));
                stmt.setString(40, requestBody.getString("sellerName"));
                stmt.setString(41, requestBody.getString("sellerPhone"));
                stmt.setString(42, requestBody.getString("sellerTaxNumber"));
                stmt.setString(43, requestBody.getString("sellerAccount"));
                stmt.setString(44, requestBody.getString("sellerAddress"));
                stmt.setString(45, requestBody.getString("sellerBank"));
                stmt.setString(46, requestBody.getString("invoiceIdNo"));
                stmt.setString(47, requestBody.getString("invoiceName"));
                stmt.setString(48, requestBody.getString("invoiceTel"));
                stmt.setString(49, requestBody.getString("invoiceTaxNo"));
                stmt.setString(50, requestBody.getString("totalAmount"));
                stmt.setString(51, requestBody.getString("rebateAmount"));
                stmt.setString(52, requestBody.getString("totalTaxAmount"));
                stmt.setString(53, requestBody.getString("taxFreeAmount"));
                stmt.setString(54, requestBody.getString("taxAmount"));
                stmt.setString(55, requestBody.getString("taxRate"));
                stmt.setString(56, requestBody.getString("invoiceNum"));
                stmt.setString(57, requestBody.getString("taxClassCode"));
                stmt.setString(58, requestBody.getString("taxClassShortName"));
                stmt.setString(59, requestBody.getString("taxOfficeCode"));
                stmt.setString(60, requestBody.getString("intactCertificateNum"));
                stmt.setString(61, requestBody.getString("invoiceStatus"));
                stmt.setString(62, requestBody.getString("originalNgaDptInvoiceNo"));
                stmt.setString(63, requestBody.getString("warrantySign"));

                stmt.executeUpdate();

            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
